﻿using System;
using System.Collections.Generic;
using System.Text;
using System.Data;
using System.Data.SqlClient;

using Dapper;
using System.Linq;
using Gx.Model.Role;//实体
using Gx.Common;//通用类

namespace Gx.Dal.Role
{
    public class M_RoleInfoDal : SqlServerHelper, IDisposable
    {
        public bool AddRoleInfo(M_RoleInfo model)
        {
            StringBuilder strSql = new StringBuilder();
            strSql.Append("INSERT INTO M_RoleInfo");
            strSql.Append("(");
            strSql.Append("RoleName,");
            strSql.Append("Status");
            strSql.Append(")");
            strSql.Append("VALUES");
            strSql.Append("(");
            strSql.Append("@RoleName,");
            strSql.Append("@Status");
            strSql.Append(")");
            using (SqlConnection conn = ConfigHelper.Open(ConfigHelper.ConnectionStrings))
            {
                return conn.Execute(strSql.ToString(), model) > 0;
            }
        }

        public bool DelRoleInfo(int RoleId)
        {
            string strSql = "UPDATE M_RoleInfo SET Status=1 WHERE RoleId=@RoleId";
 
            using (SqlConnection conn = ConfigHelper.Open(ConfigHelper.ConnectionStrings))
            {
                return conn.Execute(strSql.ToString(),new { RoleId }) > 0;
            }
        }

        public bool EditRoleInfo(M_RoleInfo model)
        {
            StringBuilder strSql = new StringBuilder();
            strSql.Append("UPDATE M_RoleInfo SET ");
            strSql.Append(" RoleName=@RoleName");
            strSql.Append(" WHERE RoleId=@RoleId AND [Status]=0");
            List<SqlParameter> pList = new List<SqlParameter>
            {
                new SqlParameter("@RoleId", SqlDbType.Int, 4) { Value = model.RoleId },
                new SqlParameter("@RoleName", SqlDbType.NVarChar, 40) { Value = model.RoleName },
            };
            using (SqlConnection conn = ConfigHelper.Open(ConfigHelper.ConnectionStrings))
            {
                return conn.Execute(strSql.ToString(), model) > 0;
            }
        }

        public List<M_RoleInfo> GetRoleInfoList(string strWhere, int pageIndex, int pageSize, out int totalCount)
        {
            totalCount = GetRoleInfoCount(strWhere);
            StringBuilder strSql = new StringBuilder();
            strSql.Append("SELECT * FROM (");
            strSql.Append("SELECT ROW_NUMBER() OVER(ORDER BY RoleId DESC) AS ORDERBYID");
            strSql.Append(",RoleId");
            strSql.Append(",RoleName");
            strSql.Append(",Status");
            strSql.AppendFormat(" FROM M_RoleInfo WHERE Status=0  {0}) AS T WHERE T.ORDERBYID  BETWEEN {1} AND {2}", strWhere, pageIndex, pageSize);
            using (SqlConnection conn = ConfigHelper.Open(ConfigHelper.ConnectionStrings))
            {
                List<M_RoleInfo> list = conn.Query<M_RoleInfo>(strSql.ToString()).ToList();
                return list;
            }
        }

        public int GetRoleInfoCount(string strWhere)
        {
            StringBuilder strSql = new StringBuilder();
            strSql.AppendFormat("SELECT COUNT(*) FROM M_RoleInfo WHERE  Status=0 {0}", strWhere);
            using (SqlConnection conn = ConfigHelper.Open(ConfigHelper.ConnectionStrings))
            {
                object obj = conn.ExecuteScalar(strSql.ToString());
                return obj.IntParse();
            };
        }

        public List<M_RoleInfo> GetRoleInfoList()
        {
            StringBuilder strSql = new StringBuilder();
            strSql.Append("SELECT");
            strSql.Append(" RoleId");
            strSql.Append(",RoleName");
            strSql.Append(",Status");
            strSql.Append(" FROM M_RoleInfo WHERE [Status]=0 ");
            using (SqlConnection conn = ConfigHelper.Open(ConfigHelper.ConnectionStrings))
            {
                List<M_RoleInfo> list = conn.Query<M_RoleInfo>(strSql.ToString()).ToList();
                return list;
            }
        }

        public M_RoleInfo GetM_RoleInfoModel(int RoleId)
        {
            StringBuilder strSql = new StringBuilder();
            strSql.Append("SELECT ");
            strSql.Append("RoleId");
            strSql.Append(",RoleName");
            strSql.Append(",Status");
            strSql.Append(" FROM M_RoleInfo WITH(NOLOCK) WHERE RoleId=@RoleId");
            using (SqlConnection conn = ConfigHelper.Open(ConfigHelper.ConnectionStrings))
            {
                M_RoleInfo model = conn.Query<M_RoleInfo>(strSql.ToString(), new { RoleId }).SingleOrDefault();
                return model;
            }
        }
    }
}
